"""
比对及修复表
"""
import datetime
from io import BytesIO

from flask import request, jsonify, Response, render_template
from openpyxl.workbook import Workbook

from tzq.database_compare_system import dcs_bp
from tzq.database_compare_system.models.dcs_task_models import JobTableRepare
from tzq.extensions import db


# 根据比对任务，比对并修复表，layui多条件分页查询接口
@dcs_bp.route('/api/jobTableRepares', methods=['GET'])
def get_job_table_repares():
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 10))

    # 获取搜索参数
    comp_task_name = request.values.get("comp_task_name")
    schema = request.values.get("schema")
    table_name = request.values.get("table_name")
    table_name_check_result = request.values.get("table_name_check_result")
    table_name1 = request.values.get("table_name1")
    table_name2 = request.values.get("table_name2")
    table_name3 = request.values.get("table_name3")
    table_name4 = request.values.get("table_name4")
    table_name5 = request.values.get("table_name5")
    table_name6 = request.values.get("table_name6")
    table_name7 = request.values.get("table_name7")

    # 基础查询
    query = JobTableRepare.query

    # 应用搜索条件
    if comp_task_name:
        query = query.filter(JobTableRepare.comp_task_name.ilike(f'%{comp_task_name}%'))
    if schema:
        query = query.filter(JobTableRepare.schema.ilike(f'%{schema}%'))
    if table_name:
        query = query.filter(JobTableRepare.table_name.ilike(f'%{table_name}%'))
    if table_name_check_result:
        query = query.filter(JobTableRepare.table_name_check_result.ilike(f'%{table_name_check_result}%'))
    if table_name1:
        query = query.filter(JobTableRepare.table_name1.ilike(f'%{table_name1}%'))
    if table_name2:
        query = query.filter(JobTableRepare.table_name2.ilike(f'%{table_name2}%'))
    if table_name3:
        query = query.filter(JobTableRepare.table_name3.ilike(f'%{table_name3}%'))
    if table_name4:
        query = query.filter(JobTableRepare.table_name4.ilike(f'%{table_name4}%'))
    if table_name5:
        query = query.filter(JobTableRepare.table_name5.ilike(f'%{table_name5}%'))
    if table_name6:
        query = query.filter(JobTableRepare.table_name6.ilike(f'%{table_name6}%'))
    if table_name7:
        query = query.filter(JobTableRepare.table_name7.ilike(f'%{table_name7}%'))

    # 获取排序参数
    sort_field = request.args.get('field', 'creation_date')
    sort_order = request.args.get('order', 'desc')

    # 构建排序
    if hasattr(JobTableRepare, sort_field):
        if sort_order == 'asc':
            order_by = getattr(JobTableRepare, sort_field).asc()
        else:
            order_by = getattr(JobTableRepare, sort_field).desc()
        query = query.order_by(order_by)
    else:
        query = query.order_by(JobTableRepare.job_table_repare_id.desc())

    # 分页查询
    pagination = query.paginate(page=page, per_page=limit, error_out=False)

    # 构建Layui要求的JSON格式
    return jsonify({
        'code': 0,
        'msg': '',
        'count': pagination.total,
        'data': [dataInfo.to_dict() for dataInfo in pagination.items]
    })


@dcs_bp.route('/api/exportJobTableRepare', methods=['GET', 'POST'])
def export_job_table_repare():
    """ 【模型比对修复】数据导出 """
    # 获取搜索参数 , POST方法 用 form.get
    comp_task_name = request.values.get("comp_task_name")
    schema = request.values.get("schema")
    table_name = request.values.get("table_name")
    table_name_check_result = request.values.get("table_name_check_result")
    table_name1 = request.values.get("table_name1")
    table_name2 = request.values.get("table_name2")
    table_name3 = request.values.get("table_name3")
    table_name4 = request.values.get("table_name4")
    table_name5 = request.values.get("table_name5")
    table_name6 = request.values.get("table_name6")
    table_name7 = request.values.get("table_name7")
    # 基础查询
    query = JobTableRepare.query

    # 应用搜索条件
    if comp_task_name:
        query = query.filter(JobTableRepare.comp_task_name.ilike(f'%{comp_task_name}%'))
    if schema:
        query = query.filter(JobTableRepare.schema.ilike(f'%{schema}%'))
    if table_name:
        query = query.filter(JobTableRepare.table_name.ilike(f'%{table_name}%'))
    if table_name_check_result:
        query = query.filter(JobTableRepare.table_name_check_result.ilike(f'%{table_name_check_result}%'))
    if table_name1:
        query = query.filter(JobTableRepare.table_name1.ilike(f'%{table_name1}%'))
    if table_name2:
        query = query.filter(JobTableRepare.table_name2.ilike(f'%{table_name2}%'))
    if table_name3:
        query = query.filter(JobTableRepare.table_name3.ilike(f'%{table_name3}%'))
    if table_name4:
        query = query.filter(JobTableRepare.table_name4.ilike(f'%{table_name4}%'))
    if table_name5:
        query = query.filter(JobTableRepare.table_name5.ilike(f'%{table_name5}%'))
    if table_name6:
        query = query.filter(JobTableRepare.table_name6.ilike(f'%{table_name6}%'))
    if table_name7:
        query = query.filter(JobTableRepare.table_name7.ilike(f'%{table_name7}%'))

    # 获取排序参数
    sort_field = request.args.get('field', 'job_table_repare_id')
    sort_order = request.args.get('order', 'desc')

    # 构建排序
    if hasattr(JobTableRepare, sort_field):
        if sort_order == 'asc':
            order_by = getattr(JobTableRepare, sort_field).asc()
        else:
            order_by = getattr(JobTableRepare, sort_field).desc()
        query = query.order_by(order_by)
    else:
        query = query.order_by(JobTableRepare.job_table_repare_id.desc())

    # 分页查询
    query = query.paginate(page=1, per_page=1000000, error_out=False)

    # 创建 Excel 工作簿和工作表
    wb = Workbook()
    ws = wb.active
    ws.title = "比对任务表模型修复"

    # 写入表头
    ws.append(['ID', '比对任务ID', '比对任务名称', 'schema', '表名', '检查表', '表1', '表2', '数据源2修复脚本', '表3',
               '数据源3修复脚本', '表4', '数据源4修复脚本', '表5', '数据源5修复脚本', '表6', '数据源6修复脚本', '表7',
               '数据源7修复脚本', '更新时间'])

    # 写入数据
    for item in query:
        ws.append([item.job_table_repare_id,
                   item.comp_task_id,
                   item.comp_task_name,
                   item.schema,
                   item.table_name,
                   item.table_name_check_result,
                   item.table_name1,
                   item.table_name2,
                   item.compare_scripts_of_source2,
                   item.table_name3,
                   item.compare_scripts_of_source3,
                   item.table_name4,
                   item.compare_scripts_of_source4,
                   item.table_name5,
                   item.compare_scripts_of_source5,
                   item.table_name6,
                   item.compare_scripts_of_source6,
                   item.table_name7,
                   item.compare_scripts_of_source7,
                   item.creation_date])

    date_string = datetime.datetime.now().strftime("%Y%m%d-%H%M%S")

    # 将工作簿写入 BytesIO 流
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    # 使用 Response 返回 Excel 文件
    return Response(
        output.getvalue(),
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={
            "Content-Disposition": "attachment; filename=" + date_string + "-job_table_repare.xlsx"
        }
    )


@dcs_bp.route('/api/jobTableRepareGetTaskNames', methods=['GET'])
def job_table_repare_get_task_names():
    """
    从PostgreSQL获取 comp_task_name 下拉框的数据，提供json数据的api接口
    【模型比对修复】查询页面 的 【任务名】下拉框 展示
    """
    try:
        # 查询去重后的 source 字段值
        distinct_comp_task_names = db.session.query(JobTableRepare.comp_task_name).distinct().all()
        # 提取结果（因为返回的是元组列表）
        comp_task_names_data = [{"comp_task_name": item[0]} for item in distinct_comp_task_names]  # 提取元组中的第一个元素
        return jsonify({"code": 0, "msg": "", "count": len(comp_task_names_data), "data": comp_task_names_data})
    except Exception as e:
        print("Database error:", e)
        return jsonify({"code": -1, "msg": str(e), "count": 0, "data": None})


@dcs_bp.route('/jobTableRepareCodeView2', methods=['GET', 'POST'])
def job_table_repare_code_view2():
    if request.method == "GET":
        id_string = request.args.get("id")
        instance_tmp = JobTableRepare.query.get_or_404(str(id_string))
    return render_template('dcs_code_viewer.html', content=instance_tmp.compare_scripts_of_source2)


@dcs_bp.route('/jobTableRepareCodeView3', methods=['GET', 'POST'])
def job_table_repare_code_view3():
    if request.method == "GET":
        id_string = request.args.get("id")
        instance_tmp = JobTableRepare.query.get_or_404(str(id_string))
    return render_template('dcs_code_viewer.html', content=instance_tmp.compare_scripts_of_source3)


@dcs_bp.route('/jobTableRepareCodeView4', methods=['GET', 'POST'])
def job_table_repare_code_view4():
    if request.method == "GET":
        id_string = request.args.get("id")
        instance_tmp = JobTableRepare.query.get_or_404(str(id_string))
    return render_template('dcs_code_viewer.html', content=instance_tmp.compare_scripts_of_source4)


@dcs_bp.route('/jobTableRepareCodeView5', methods=['GET', 'POST'])
def job_table_repare_code_view5():
    if request.method == "GET":
        id_string = request.args.get("id")
        instance_tmp = JobTableRepare.query.get_or_404(str(id_string))
    return render_template('dcs_code_viewer.html', content=instance_tmp.compare_scripts_of_source5)


@dcs_bp.route('/jobTableRepareCodeView6', methods=['GET', 'POST'])
def job_table_repare_code_view6():
    if request.method == "GET":
        id_string = request.args.get("id")
        instance_tmp = JobTableRepare.query.get_or_404(str(id_string))
    return render_template('dcs_code_viewer.html', content=instance_tmp.compare_scripts_of_source6)


@dcs_bp.route('/jobTableRepareCodeView7', methods=['GET', 'POST'])
def job_table_repare_code_view7():
    if request.method == "GET":
        id_string = request.args.get("id")
        instance_tmp = JobTableRepare.query.get_or_404(str(id_string))
    return render_template('dcs_code_viewer.html', content=instance_tmp.compare_scripts_of_source7)
